﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Configuration;
using System.Reflection;
using WB.Entity.Helper;
using WB.Library;
namespace WB.DaoHelper
{
    public class BaseProvider
    {
        public static string ConnectionString
        {
            get
            {
                return ConfigurationManager.ConnectionStrings["ConnectString"].ConnectionString;
            }
        }

        string connectionString;

        public BaseProvider()
        {
            connectionString = ConnectionString;
        }

        SqlParameter[] ConvertSqlParam(Hashtable hashParam)
        {
            SqlParameter[] param = new SqlParameter[hashParam.Count + 3];
            int i = 0;
            foreach (DictionaryEntry en in hashParam)
            {
                param[i++] = new SqlParameter("@" + en.Key.ToString(), en.Value);
            }
            return param;
        }

        protected PagedData GetPagging(string cmdText, int pageindex)
        {
            return GetPagging(cmdText, null, pageindex);
        }

        protected PagedData GetPagging(string cmdText, Hashtable param, int pageindex)
        {
            PagedData pagging = new PagedData();
            pagging.PageSize = PublicContants.PaggingRow;

            if (param == null) param = new Hashtable();
            int i = param.Count;
            
            SqlParameter[] sqlParam = ConvertSqlParam(param);
            sqlParam[i++] = new SqlParameter("@PCurrent", pageindex - 1);
            sqlParam[i++] = new SqlParameter("@PCount", PublicContants.PaggingRow);
            sqlParam[i] = new SqlParameter("@PRows", SqlDbType.Int);
            sqlParam[i].Direction = ParameterDirection.Output;

            pagging.DataSource = ExecuteDataTable(CommandType.StoredProcedure, cmdText, sqlParam);
            if (sqlParam[i].Value != null)
                pagging.PageSize = (int)sqlParam[i].Value;

            return pagging;
        }

        #region Select, Insert, Update
        private class FieldInfo
        {
            public string Name { get; set; }
            public string DataType { get; set; }
            public bool IsKey { get; set; }
            public int MaxLength { get; set; }

            public static FieldInfo Parse(string field)
            {
                FieldInfo info = new FieldInfo();
                string[] props = field.Split(new char[] { '@' }, StringSplitOptions.RemoveEmptyEntries);
                bool t1 = false; int t2 = 0;
                for (int i = 0; i < props.Length; i++)
                {
                    string[] attr = props[i].Split(':');
                    switch (attr[0].ToLower())
                    {
                        case "name":
                            info.Name = attr[1];
                            break;
                        case "datatype":
                            info.DataType = attr[1];
                            break;
                        case "iskey":
                            bool.TryParse(attr[1], out t1);
                            info.IsKey = t1;
                            break;
                        case "length":
                            int.TryParse(attr[1], out t2);
                            info.MaxLength = t2;
                            break;
                    }
                }
                return info;
            }
        }

        protected object SelectByKey(object obj, params object[] colsvalues)
        {
            string strSQL = "Select * " +
                            "From {0} " +
                            "Where ";
            string tableName = obj.GetType().InvokeMember("TableName", BindingFlags.GetField | BindingFlags.NonPublic | BindingFlags.Static | BindingFlags.DeclaredOnly,
                        null, obj, null).ToString();
            strSQL = string.Format(strSQL, tableName);
            Hashtable param = new Hashtable();
            int t = (colsvalues.Length / 2);
            string colName = "";
            for (int i = 0; i < colsvalues.Length; i += 2)
            {
                if (colsvalues[i].GetType() == typeof(string))
                    colName = colsvalues[i].ToString();
                else
                {
                    Field f = (Field)colsvalues[i];
                    colName = f.Name;
                }
                string typeinfo = obj.GetType().InvokeMember("_" + colName + "Property", BindingFlags.GetField | BindingFlags.NonPublic | BindingFlags.Static | BindingFlags.DeclaredOnly, null, obj, null).ToString();
                FieldInfo info = FieldInfo.Parse(typeinfo);
                strSQL += info.Name + " = @" + colName + " and ";
                param[colName] = colsvalues[i + 1];
            }
            strSQL = strSQL.Substring(0, strSQL.Length - 4);
            DataTable tbl = ExecuteDataTable(param, CommandType.Text, strSQL);
            if (tbl != null && tbl.Rows.Count > 0)
            {
                object returnobj = Activator.CreateInstance(obj.GetType());
                PropertyInfo[] props = returnobj.GetType().GetProperties();
                for (int i = 0; i < props.Length; i++)
                {
                    string typeinfo = obj.GetType().InvokeMember("_" + props[i].Name + "Property", BindingFlags.GetField | BindingFlags.NonPublic | BindingFlags.Static | BindingFlags.DeclaredOnly, null, obj, null).ToString();
                    FieldInfo info = FieldInfo.Parse(typeinfo);
                    if (!Convert.IsDBNull(tbl.Rows[0][info.Name]))
                    {
                        PropertyInfo p = returnobj.GetType().GetProperty(props[i].Name);
                        p.SetValue(returnobj, tbl.Rows[0][info.Name], null);
                    }
                }
                MethodInfo med = returnobj.GetType().GetMethod("ResetDirty");
                med.Invoke(returnobj, null);
                return returnobj;
            }
            return null;
        }

        protected object ObjectInsert(object obj)
        {
            PropertyInfo[] props = obj.GetType().GetProperties();

            List<SqlParameter> param = new List<SqlParameter>();
            List<object> cols = new List<object>();
            string into = "";
            string values = "";
            for (int i = 0; i < props.Length; i++)
            {
                bool isdirty = (bool)obj.GetType().InvokeMember("_" + props[i].Name + "IsDirty", BindingFlags.GetField | BindingFlags.Instance | BindingFlags.NonPublic, null, obj, null);
                string typeinfo = obj.GetType().InvokeMember("_" + props[i].Name + "Property", BindingFlags.GetField | BindingFlags.NonPublic | BindingFlags.Static | BindingFlags.DeclaredOnly, null, obj, null).ToString();
                FieldInfo info = FieldInfo.Parse(typeinfo);
                if (isdirty)
                {

                    into += info.Name + ",";
                    values += "@" + props[i].Name + ",";

                    SqlParameter pa = new SqlParameter("@" + props[i].Name, props[i].GetValue(obj, null));
                    if (info.MaxLength > 0)
                        pa.Size = info.MaxLength;
                    param.Add(pa);
                    if (info.IsKey)
                    {
                        cols.Add(props[i].Name);
                        cols.Add(props[i].GetValue(obj, null));
                    }
                }
                else
                {
                    if (info.IsKey)
                    {
                        if (info.DataType == "uniqueidentifier")
                        {
                            Guid id = Guid.NewGuid();
                            into += info.Name + ",";
                            values += "@" + props[i].Name + ",";

                            SqlParameter pa = new SqlParameter("@" + props[i].Name, id);
                            param.Add(pa);

                            cols.Add(props[i].Name);
                            cols.Add(id);
                        }
                        else if (info.DataType.Contains("int"))
                        {
                            cols.Add(props[i].Name);
                            cols.Add(0);
                        }
                    }
                }
            }
            if (param.Count > 0)
            {
                string tableName = obj.GetType().InvokeMember("TableName", BindingFlags.GetField | BindingFlags.NonPublic | BindingFlags.Static | BindingFlags.DeclaredOnly,
                        null, obj, null).ToString();
                string strSQL = "Insert Into {0} ({1}) Values ({2})";
                strSQL = string.Format(strSQL, tableName, into.TrimEnd(','), values.TrimEnd(','));
                SqlParameter[] pr = new SqlParameter[param.Count];
                for (int i = 0; i < param.Count; i++)
                {
                    pr[i] = param[i];
                    if (param[i] == null) pr[i].Value = DBNull.Value;
                }
                ExecuteNonQuery(CommandType.Text, strSQL, pr);

                if (cols.Count > 0)
                {
                    object[] pp = new object[cols.Count];
                    for (int i = 0; i < cols.Count; i++)
                        pp[i] = cols[i];
                    return SelectByKey(obj, pp);
                }
                return obj;
            }
            return null;
        }

        protected void ObjectUpdate(object obj)
        {
            PropertyInfo[] props = obj.GetType().GetProperties();
            List<SqlParameter> param = new List<SqlParameter>();
            string sset = "";
            string wwhere = "";
            for (int i = 0; i < props.Length; i++)
            {
                bool isdirty = (bool)obj.GetType().InvokeMember("_" + props[i].Name + "IsDirty", BindingFlags.GetField | BindingFlags.Instance | BindingFlags.NonPublic, null, obj, null);
                string typeinfo = obj.GetType().InvokeMember("_" + props[i].Name + "Property", BindingFlags.GetField | BindingFlags.NonPublic | BindingFlags.Static | BindingFlags.DeclaredOnly, null, obj, null).ToString();
                FieldInfo info = FieldInfo.Parse(typeinfo);
                if (info.IsKey)
                {
                    wwhere += info.Name + " = @" + props[i].Name + " and ";
                    SqlParameter pa = new SqlParameter("@" + props[i].Name, props[i].GetValue(obj, null));
                    param.Add(pa);
                }
                else
                {
                    if (isdirty)
                    {
                        sset += info.Name + " = @" + props[i].Name + ",";

                        SqlParameter pa = new SqlParameter("@" + props[i].Name, props[i].GetValue(obj, null));
                        if (props[i].GetValue(obj, null) == null)
                            pa.Value = DBNull.Value;
                        if (info.MaxLength > 0)
                            pa.Size = info.MaxLength;
                        param.Add(pa);
                    }
                }
            }
            if (param.Count > 0 && !string.IsNullOrEmpty(sset) && !string.IsNullOrEmpty(wwhere))
            {
                string tableName = obj.GetType().InvokeMember("TableName", BindingFlags.GetField | BindingFlags.NonPublic | BindingFlags.Static | BindingFlags.DeclaredOnly,
                        null, obj, null).ToString();
                string strSQL = "Update {0} Set {1} Where {2}";
                strSQL = string.Format(strSQL, tableName, sset.TrimEnd(','), wwhere.Substring(0, wwhere.Length - 4));
                SqlParameter[] pr = new SqlParameter[param.Count];
                for (int i = 0; i < param.Count; i++)
                    pr[i] = param[i];
                ExecuteNonQuery(CommandType.Text, strSQL, pr);
            }
        }
        #endregion

        #region Exec Command Sql
        protected void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
        {

            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if (trans != null)
                cmd.Transaction = trans;

            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (SqlParameter parm in cmdParms)
                    cmd.Parameters.Add(parm);
            }
        }

        void PrepareCommand(SqlCommand cmd, SqlConnection conn, SqlTransaction trans, CommandType cmdType, string cmdText, Hashtable cmdParms)
        {
            if (conn.State != ConnectionState.Open)
                conn.Open();

            cmd.Connection = conn;
            cmd.CommandText = cmdText;

            if (trans != null)
                cmd.Transaction = trans;

            cmd.CommandType = cmdType;

            if (cmdParms != null)
            {
                foreach (DictionaryEntry en in cmdParms)
                {
                    SqlParameter para = new SqlParameter("@" + en.Key, en.Value);
                    cmd.Parameters.Add(para);
                }
            }
        }

        protected int ExecuteNonQuery(CommandType cmdType, string cmdText)
        {
            return ExecuteNonQuery(cmdType, cmdText, null);
        }

        protected int ExecuteNonQuery(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            int val = 0;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand();

                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                conn.Close();
            }
            return val;
        }

        protected int ExecuteNonQuery(CommandType cmdType, string cmdText, int TimeOut, params SqlParameter[] commandParameters)
        {
            int val = 0;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand();

                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                cmd.CommandTimeout = TimeOut;
                val = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                conn.Close();
            }
            return val;
        }

        //Thực hiện việc lấy dữ liệu trong CSDL
        protected DataTable ExecuteDataTable(CommandType cmdType, string cmdText, params SqlParameter[] commandParameters)
        {
            DataTable dt = new DataTable();
            //using (SqlConnection conn = new SqlConnection(connectionString))
            //{

            //    SqlCommand cmd = new SqlCommand();

            //    PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
            //    SqlDataAdapter da = new SqlDataAdapter(cmd);
            //    da.Fill(dt);
            //    da.Dispose();
            //    cmd.Parameters.Clear();
            //    conn.Close();
            //}

            return dt;
        }
        protected DataTable ExecuteDataTable(CommandType cmdType, string cmdText, int timeOut, params SqlParameter[] commandParameters)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandTimeout = timeOut;
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                da.Dispose();
                cmd.Parameters.Clear();
                conn.Close();
            }
            return dt;
        }
        protected DataTable ExecuteDataTable(CommandType cmdType, string cmdText)
        {
            return ExecuteDataTable(cmdType, cmdText, null);
        }

        protected DataTable ExecuteDataTable(Hashtable commandParameters, CommandType cmdType, string cmdText)
        {
            DataTable dt = new DataTable();
            //using (SqlConnection conn = new SqlConnection(connectionString))
            //{
            //    SqlCommand cmd = new SqlCommand();
            //    PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
            //    SqlDataAdapter da = new SqlDataAdapter(cmd);
            //    da.Fill(dt);
            //    da.Dispose();
            //    cmd.Parameters.Clear();
            //    conn.Close();
            //}
            return dt;
        }
        protected DataTable ExecuteDataTable(Hashtable commandParameters, CommandType cmdType, string cmdText, int timeOut)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.CommandTimeout = timeOut;
                PrepareCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(dt);
                da.Dispose();
                cmd.Parameters.Clear();
                conn.Close();
            }
            return dt;
        }
        #endregion
    }
}
